package com.kaishengit.pms.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class DBHelp<T> {
	
	private static BasicDataSource ds = new BasicDataSource();
	
	//�Ѵ������ӳط��ھ�̬���� 
	static{
		Properties properties = new Properties();
		
		try {
			properties.load(DBHelp.class.getClassLoader().getResourceAsStream("dbconfig.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		String driver = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		
		ds.setDriverClassName(driver);
		ds.setUrl(url);
		ds.setUsername(username);
		ds.setPassword(password);
		ds.setInitialSize(5);
		ds.setMaxWait(5000);
		ds.setMaxActive(20);
		ds.setMinIdle(10);
		System.out.println("数据库连接初始化....");
	}
	
	
	/**
	 * ��ȡ��ݿ�����
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException {
		Connection conn = ds.getConnection();
		return conn;
	}
	
	/**
	 * ִ����ɾ��
	 * @param sql
	 * @param args
	 * @return ��Ӱ�������
	 */
	public int excuteSQL(String sql,Object...args) {
		int rows = 0;
		Connection conn = null;
		PreparedStatement stat = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++) {
				stat.setObject(i + 1, args[i]);
			}
			rows = stat.executeUpdate();
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stat,conn);
		} 
		
		return rows;
	}

	/**
	 * �����û�������Ϣ
	 * @param sql
	 * @param mapper
	 * @param args
	 * @return
	 */
	public T queryForObject(String sql,MapperRow<T> mapper,Object...args) {
		T obj = null;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			if(rs.next()) {
				obj = mapper.rowMapper(rs);
			}
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return obj;
	}
	
	/**
	 * ���ض��󼯺�
	 * @param sql
	 * @param mapper
	 * @param args
	 * @return
	 */
	public List<T> queryForList(String sql,MapperRow<T> mapper,Object...args) {
		List<T> list = new ArrayList<T>();
		T obj = null;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			while(rs.next()) {
				obj =  mapper.rowMapper(rs);
				list.add(obj);
			}
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return list;
	}
	/**
	 * �ر���ݿ�����
	 * @param stat
	 * @param conn
	 */
	private void close(PreparedStatement stat, Connection conn) {
		close(null,stat,conn);
	}

	private void close(ResultSet rs, PreparedStatement stat, Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null) {
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
